/****** Object: View [dbo].[vw_4_7_2]   Script Date: 2014-01-19 21:48:09 ******/
USE [SHMIS];
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_4_7_2]'))
BEGIN
DROP VIEW [dbo].[vw_4_7_2];
END
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[vw_4_7_2]
AS
                SELECT dar.Duration_Begin + '~' + dar.Duration_End AS Duration,
                       isnull (CASE dar.Amount_Type WHEN '03' THEN dar.Amount END, 0)
                          AS Properties,
                       isnull (CASE dar.Amount_Type WHEN '01' THEN dar.Amount END, 0)
                          AS HouseFee,
                       isnull (CASE dar.Amount_Type WHEN '02' THEN dar.Amount END, 0)
                          AS EquipFee,
                       isnull (dmm.Actual_Date, '1899-12-31') AS Actual_Date,
                       isnull (
                          CASE dmm.Deduction_Type WHEN '01' THEN dmm.Actual_Amount END,
                          0)
                          AS Actual_HouseFee,
                       isnull (
                          CASE dmm.Deduction_Type WHEN '02' THEN dmm.Actual_Amount END,
                          0)
                          AS Actual_EquipFee,
                       isnull (
                          CASE dmm.Deduction_Type WHEN '03' THEN dmm.Actual_Amount END,
                          0)
                          AS Actual_Properties,
                       Actual_Balance,
                       dar.Adjust_Serial_Num,
                       dar.Customer_Code,
                       dar.Contract_Num,
                       dar.Serial_Num
                  FROM vw_DeductibleAR dar
                       LEFT JOIN HD_DeductibleMoneyManage dmm
                       ON     dar.Customer_Code = dmm.Customer_Code
                          AND dar.Contract_Num = dmm.Contract_Num
                          AND dar.Serial_Num = dmm.ARSerial_Num
                 WHERE     dar.Record_Check = '1'
                       AND dar.Verified = '1'
                       AND dar.Deleted = '0'
GO

